package com.yellowpage.bigdata.service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.map.LinkedMap;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.opensymphony.xwork2.ActionContext;
import com.yellowpage.white.common.Constants;
import com.yellowpage.white.common.PageRender;
import com.yellowpage.white.dao.QueryAssistantSQL;
import com.yellowpage.white.service.BaseServiceImpl;
import com.yellowpage.bigdata.data.NspFatallogRequsetSum;
import com.yellowpage.bigdata.dao.NspFatallogRequsetSumDao;
import com.yellowpage.ca.data.User;
import com.yellowpage.ca.service.RegionService;

public class NspFatallogRequsetSumServiceImpl extends BaseServiceImpl implements
		NspFatallogRequsetSumService {
	private NspFatallogRequsetSumDao nspFatallogRequsetSumDao;
	public void setNspFatallogRequsetSumDao(
			NspFatallogRequsetSumDao nspFatallogRequsetSumDao) {
		this.nspFatallogRequsetSumDao = nspFatallogRequsetSumDao;
	}

	/* Smooth generate Method */
	public NspFatallogRequsetSum getNspFatallogRequsetSum(Long id) {
		return nspFatallogRequsetSumDao.get(id);
	}

	public List<NspFatallogRequsetSum> getAllNspFatallogRequsetSum() {
		return nspFatallogRequsetSumDao.getAll();
	}

	public List<Map> queryNspFatallogRequsetSumByPage(Map queryMap,
			PageRender pageRender) {
		String t2 = queryMap.get("regionCode").equals("") ? "" : " and REGION_CODE in "+queryMap.get("regionCode");
		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(
						"SELECT a.REGION_CODE , "
								+ "(select c.name from OMC_REGION c where c.REGION_CODE=a.region_code) as REGION_NAME,"
								+ "a.DATA_FLAG , a.LOG_DATE , a.CREATE_USER , a.CREATE_TIME , a.NOTE , a.FATALLOG_REQUSET_SUM_ID , "
								+ "(select c.COMPANY from NSP_REQUEST_TYPE c where c.CODE = a.REQUEST_TYPE) as REQUEST_TYPE ,"
								+ " a.REQUEST_COUNT    FROM NSP_FATALLOG_REQUSET_SUM a WHERE 1=1 " + t2)
				.addCondition(" and LOG_DATE >= :logDate ")
				.addCondition(" and LOG_DATE <= :query_logDate_end ")
				.addCondition(" and REQUEST_TYPE = :requestType ")
				.addCondition(" and DATA_FLAG = :query_dataFlag ");
		List<Map> resultList = sqlAssistant.queryByPage(
				baseDao.getConnection(), queryMap, pageRender);

		return resultList;
	}

	public List<Map> queryregion(Map queryMap) {

		QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(
				"SELECT c.REGION_CODE FROM OMC_REGION c WHERE 1=1")
				.addCondition(" and c.NAME like :regionCode ");

		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(),
				queryMap);

		return resultList;
	}

	@Override
	public Map<String,String> getrequestTypeMap() {
		Map<String,String> dataSourceMap = new LinkedMap();
		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(" SELECT a.COMPANY as NAME, a.CODE as VALUE  FROM NSP_REQUEST_TYPE a  ");
		sqlAssistant.setTail("order by (a.CODE+0)");
		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(),
				new HashMap());
		for (int i = 0; i < resultList.size(); i++) {
			dataSourceMap.put(resultList.get(i).get("VALUE").toString(), resultList.get(i)
					.get("VALUE") + "-" + resultList.get(i).get("NAME"));
		}
		return dataSourceMap;
	}

	@Override
	public List<Map> queryNspFatallogDayCountByPage(Map queryMap,
			PageRender pageRender) {

		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(
						"select SU.LOG_DATE,SU.REQUEST_TYPE,(select c.COMPANY from NSP_REQUEST_TYPE c where c.CODE = SU.REQUEST_TYPE) as REQUEST_TYPE_NAME, "
								+ "sum(SU.a) as REQUEST_COUNT, sum(SU.imei) as IMEI_REQUEST_COUNT, sum(SU.noimei) as NO_IMEI_REQUEST_COUNT,"
								+ "X.imei_day_count, X.imei_all_count, X.imei_new_count "
								+ "from (select c.LOG_DATE, c.request_type, "
								+ "if(data_flag='A', sum(request_count),0 ) as a,"
								+ "if(data_flag='IMEI', sum(request_count),0 ) as imei, "
								+ "if(data_flag='NOIMEI', sum(request_count),0 ) as noimei "
								+ "from NSP_FATALLOG_REQUSET_SUM c "
								+ "GROUP BY  c.LOG_DATE, c.request_type, c.data_flag) SU "
								+ "left JOIN NSP_FATALLOG_IMEI_DAYREGION_SUM X on SU.LOG_DATE = X.LOG_DATE "
								+ "and SU.request_type = X.request_type and X.region_code='A' "
								+ "where (SU.REQUEST_TYPE='X' OR SU.REQUEST_TYPE not in(select `code` from NSP_CHANNEL)) ")
				.addCondition(" and SU.LOG_DATE >= :logDate ")
				.addCondition(" and SU.LOG_DATE <= :query_logDate_end ")
				.addCondition(" and SU.REQUEST_TYPE = :requestType ");
		sqlAssistant.setTail(" group by SU.LOG_DATE, SU.request_type");
		List<Map> resultList = sqlAssistant.queryByPage(
				baseDao.getConnection(), queryMap, pageRender);

		return resultList;
	}
	
	@Override
	public Map<String,String> getChannelMap() {
		Map<String,String> dataSourceMap = new LinkedMap();
		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(" SELECT a.code as NAME, a.name as VALUE  FROM NSP_CHANNEL a  ");
		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(),
				new HashMap());
		for (int i = 0; i < resultList.size(); i++) {
			dataSourceMap.put(resultList.get(i).get("NAME").toString(), resultList.get(i).get("VALUE").toString());
		}
		return dataSourceMap;
	}
	
	@Override
	public String getrequestTypeString(String query_requestType) {
		String val = "";
		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(" select c.`CODE` as NAME from NSP_REQUEST_TYPE c where c.channel_code in ("+query_requestType+") ");
		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(),
				new HashMap());
		for (int i = 0; i < resultList.size(); i++) {
		    val += "'" + resultList.get(i).get("NAME").toString()+ "'," ;
		}
		String substring="";
		if(val.length()>0){
			 substring = val.substring(0, val.length() - 1);
		}else{
			substring="'xxxxxx'";
		}
		
		return substring;
	}
	
	@Override
	public Map<String,String> getRegionMap(String regionSql) {
		Map<String,String> dataSourceMap = new LinkedMap();
		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(" select a.REGION_CODE as NAME, a.SHORT_NAME as VALUE from OMC_REGION a where 1 = 1 "+ regionSql);
		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(),
				new HashMap());
		for (int i = 0; i < resultList.size(); i++) {
			dataSourceMap.put(resultList.get(i).get("NAME").toString(), resultList.get(i).get("VALUE").toString());
		}
		return dataSourceMap;
	}
	
	@Override
	public List<Map> queryNspAbilityRequestDay(Map<String,String> queryMap,
			PageRender pageRender) {
		String t1 = queryMap.get("requestType").equals("") ? "" : " and SU.REQUEST_TYPE in ( "+queryMap.get("requestType")+") ";
		String t2 = queryMap.get("regionCode").equals("") ? "" : " and SU.REGION_CODE in ( "+queryMap.get("regionCode")+") ";
		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(
						" select SU.LOG_DATE," +
						"(select b.name from NSP_CHANNEL b where b.code=(select c.CHANNEL_CODE from NSP_REQUEST_TYPE c where c.`CODE`= SU.REQUEST_TYPE)) as channel," +
						"(select c.name from OMC_REGION c where c.region_code=SU.region_code) as region_name," +
						"(select c.COMPANY from NSP_REQUEST_TYPE c where c.CODE = SU.REQUEST_TYPE) as REQUEST_TYPE_NAME," +
						"sum(SU.allNum) as REQUEST_COUNT,sum(SU.imei) as IMEI_REQUEST_COUNT, sum(SU.noimei) as NO_IMEI_REQUEST_COUNT," +
						"sum(SU.allCount) as REQUEST_COUNT_ALL,sum(SU.imeiCount) as IMEI_REQUEST_COUNT_ALL, sum(SU.noimeiCount) as NO_IMEI_REQUEST_COUNT_ALL" +
						" from (select c.LOG_DATE,c.REQUEST_TYPE,c.REGION_CODE," +
						"if(DATA_FLAG='A', sum(REQUEST_COUNT),0) as allNum," +
						"if(DATA_FLAG='IMEI', sum(REQUEST_COUNT),0) as imei," +
						"if(DATA_FLAG='NOIMEI',sum(REQUEST_COUNT),0) as noimei," +
						"if(DATA_FLAG='A', sum(REQUEST_COUNT_ALL),0) as allCount," +
						"if(DATA_FLAG='IMEI', sum(REQUEST_COUNT_ALL),0) as imeiCount," +
						"if(DATA_FLAG='NOIMEI',sum(REQUEST_COUNT_ALL),0) as noimeiCount" +
						" from NSP_FATALLOG_REQUSET_SUM c " +
						" where (c.REQUEST_TYPE='X' OR c.REQUEST_TYPE not in(select `code` from NSP_CHANNEL))" +
						" group by c.LOG_DATE,c.REQUEST_TYPE,c.REGION_CODE,c.DATA_FLAG) SU where 1=1 "+ t1 + t2)
				.addCondition(" and SU.LOG_DATE >= :logDate ")
				.addCondition(" and SU.LOG_DATE <= :query_logDate_end ");
		sqlAssistant.setTail(" group by SU.LOG_DATE,SU.REQUEST_TYPE,SU.REGION_CODE");
		List<Map> resultList = sqlAssistant.queryByPage(
				baseDao.getConnection(), queryMap, pageRender);
		return resultList;
	}
	
	@Override
	public List<Map> queryNspAbilityRequestSumDay(Map<String,String> queryMap,
			PageRender pageRender) {
		String t1 = queryMap.get("query_logDate_end").equals("") ? " and c.LOG_DATE <= (select max(log_date) from NSP_FATALLOG_REQUSET_SUM)" : " and c.LOG_DATE <='"+queryMap.get("query_logDate_end")+"'";
		String t2 = queryMap.get("logDate").equals("") ? " and c.LOG_DATE >= (select min(log_date) from NSP_FATALLOG_REQUSET_SUM)" : " and c.LOG_DATE >='"+queryMap.get("logDate")+"'";
		String t3 = queryMap.get("requestType").equals("") ? "" : " and SU.REQUEST_TYPE in ( "+queryMap.get("requestType")+") ";
		String t4 = queryMap.get("regionCode").equals("") ? "" : " and SU.REGION_CODE in ( "+queryMap.get("regionCode")+") ";
		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(
						"select min(SU.LOG_DATE) as beginday,max(SU.LOG_DATE) as endday ," +
						"SU.REQUEST_TYPE as REQUEST_TYPE,(select c.COMPANY from NSP_REQUEST_TYPE c where c.CODE = SU.REQUEST_TYPE) as REQUEST_TYPE_NAME," +
						"(select b.name from NSP_CHANNEL b where b.code=(select c.CHANNEL_CODE from NSP_REQUEST_TYPE c where c.`CODE`= SU.REQUEST_TYPE)) as channel," +
						"SU.region_code as region_code,(select c.name from OMC_REGION c where c.region_code=SU.region_code) as region_name," +
						"max(SU.allNum) as countall,max(SU.imei) as imei,max(SU.noimei) as noimei," +
						"SUM(SU.dayallNum) as requestCount," +
						"sum(SU.dayimei) as dayimei," +
						"sum(SU.daynoimei) as daynoimei" +
						" from (select X.LOG_DATE, X.region_code,X.REQUEST_TYPE,X.allNum ,sum(X.imei) as imei, sum(X.noimei) as noimei,X.dayallNum,sum(X.dayimei) as dayimei,sum(X.daynoimei) as daynoimei" +
						" from(select c.LOG_DATE,c.REQUEST_TYPE,c.REGION_CODE," +
						"if(DATA_FLAG='A', REQUEST_COUNT_ALL,0) as allNum," +
						"if(DATA_FLAG='IMEI', REQUEST_COUNT_ALL,0) as imei," +
						"if(DATA_FLAG='NOIMEI',REQUEST_COUNT_ALL,0) as noimei," +
						"if(DATA_FLAG='A', REQUEST_COUNT,0) as dayallNum," +
						"if(DATA_FLAG='IMEI', REQUEST_COUNT,0) as dayimei," +
						"if(DATA_FLAG='NOIMEI', REQUEST_COUNT,0) as daynoimei" +
						" from NSP_FATALLOG_REQUSET_SUM c " +
						"where (c.REQUEST_TYPE='X' OR c.REQUEST_TYPE not in(select `code` from NSP_CHANNEL))" +
						""+ t1 + t2 + 
						" group by c.LOG_DATE,c.REQUEST_TYPE,c.REGION_CODE,c.DATA_FLAG) X" +
						" group by X.LOG_DATE,X.REGION_CODE,X.REQUEST_TYPE) SU where 1=1 "+ t3 + t4);
		sqlAssistant.setTail(" group by SU.region_code,SU.REQUEST_TYPE");
		List<Map> resultList = sqlAssistant.queryByPage(
				baseDao.getConnection(), queryMap, pageRender);
		return resultList;
	}

	public void saveNspFatallogRequsetSum(
			NspFatallogRequsetSum nspFatallogRequsetSum) {
		nspFatallogRequsetSumDao.save(nspFatallogRequsetSum);
	}

	public void updateNspFatallogRequsetSum(
			NspFatallogRequsetSum nspFatallogRequsetSum) {
		nspFatallogRequsetSumDao.update(nspFatallogRequsetSum);
	}

	public void delete(List deleteList) {
		nspFatallogRequsetSumDao.remove(deleteList);
	}

}